﻿
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sproc_SMS_GetUnSent]') AND type in (N'P', N'PC'))
    DROP PROCEDURE [dbo].[sproc_SMS_GetUnSent];
GO
CREATE PROCEDURE [dbo].[sproc_SMS_GetUnSent]
/*

===================================================
功能:     处理短信并获取待发送手机短信记录
参数:
返回值:
    
===================================================

*/
AS
BEGIN
SET NOCOUNT ON

DECLARE @IDS varchar(4000),@SQL varchar(4000)
SET @IDS = ''

SELECT @IDS = @IDS + CONVERT(varchar,MsgID) + ',' FROM UDS_SMS_MobileMsgSendBuffer WHERE Sendtime <= getdate() 

IF LEN(@IDS)>0
BEGIN
	--去掉后面的逗号
	SET @IDS = LEFT(@IDS, LEN(@IDS)-1)

	--返回记录集
	SET @SQL = N'SELECT * FROM UDS_SMS_MobileMsgSendBuffer WHERE MsgID in (' + @IDS +')' 
	EXEC (@SQL)    

	--将消息插入至历史记录
	SET @SQL=N'INSERT INTO
			UDS_SMS_MobileMsgSendHistory (MsgID,Sender,Content,SendTime,RecipientMobileNo)
		(SELECT MsgID,Sender,Content,getdate(),RecipientMobileNo 
			FROM UDS_SMS_MobileMsgSendBuffer WHERE MsgID in ('+@IDS+'))'
	EXEC(@SQL)

	--删除已发送记录
	SET @SQL = N'DELETE FROM UDS_SMS_MobileMsgSendBuffer WHERE MsgID in (' + @IDS +') AND RepeatTimes=0'
	EXEC (@SQL)


	--更新重复时间
	SET @SQL = N'UPDATE 
				UDS_SMS_MobileMsgSendBuffer 
				SET 
					RepeatTimes=RepeatTimes-1 , SendTime=DATEADD(mi, RepeatPeriod, SendTime)
				WHERE MsgID in (' + @IDS +') AND RepeatTimes>0'
	EXEC (@SQL)
END

END
GO